package com.ruge.test.springMVC.支持json.Oracle树形结构;

import com.fasterxml.jackson.databind.ObjectMapper;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.junit.Test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * 爱丽丝、如歌  创建于 2018/6/27 8:30
 * 说明:  TODO
 */
public class oracle树形结构测试LEVEL加加 {

    @Test
    public void test() throws Exception {

        Class.forName("oracle.jdbc.OracleDriver");
        Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.0.162:1521:entegor", "zx_proxy", "ideal");
        QueryRunner runner = new QueryRunner();
        /**
         * 定义最终的结果集
         */
        List result = new ArrayList();
        /**
         * 1 查询出agent的信息
         */
        String ip = "192.168.1.1";
        String port = "1234";
        String agentsql = "select t.IAGENTINFO_ID as id, t.IAGENT_IP as ip,IAGENT_NAME as sysname, t.IAGENT_PORT as port  from IEAI_AGENTINFO t where  t.IAGENT_IP = '" + ip + "' and t.IAGENT_PORT = '" + port + "'";
        OracleModel Agent = runner.query(conn, agentsql, new BeanHandler<OracleModel>(OracleModel.class));
        Agent.setProxy(false);
        List<OracleModel> list = new ArrayList();
        list.add(Agent);

        /**
         * 2 第一层proxy的数据查询
         */

        String firstProxysql = "select  t.iid as id, t.PROXYIP as ip,proxyname as sysname, t.PROXYPORT as port  from IEAI_PROXY_LIST t, IEAI_PROXY_LIST_RELATION n where n.PROXYNEXTTYPE = 1 and t.iid = n.PROXYID and n.PROXYNEXTID =" + Agent.getId();
        List<OracleModel> firstProxy = runner.query(conn, firstProxysql, new BeanListHandler<OracleModel>(OracleModel.class));
        List<OracleModel> listFProxy = new ArrayList();

        /**
         * 3 proxy递归迭代
         */

        for (int i = 0; i < firstProxy.size(); i++) {
            OracleModel om = (OracleModel) firstProxy.get(i);
            om.setProxy(true);
            /**
             * 如果有上级的数据  就将该数据放置在对象中
             */
            om.getNodes().add(list);
            List<OracleModel> oracleModels = this.proxyTree(om.getId(), conn, om);
            result.add(oracleModels);
        }
        System.out.println(result);

        List<OracleModel> li = new ArrayList<OracleModel>();


        List<OracleModel> oracleModel2 = new ArrayList<OracleModel>();
        if (null != result && !result.isEmpty()) {
            List proxyList = (List) result.get(0);
            for (int i = 0; i < proxyList.size(); i++) {
                OracleModel om = null;
                List<OracleModel> obj = (List<OracleModel>) proxyList.get(i);
                if (obj.size() > 1) {
                    for (int j = 0; j < obj.size(); j++) {
                        if (obj.get(j) instanceof OracleModel) {
                            om = (OracleModel) obj.get(i);
                        } else {
                            om = getOracleModel((List) obj.get(j));
                        }
                        li.add(om);
                    }
                } else {
                    om = getOracleModel((List) proxyList.get(i));
                    li.add(om);
                }
            }
        }

        /**
         * 重新获取level赋值后的li集合
         */
        int level = 1;
        List<OracleModel> resList = new ArrayList<OracleModel>();

        for (int i = 0; i < li.size(); i++) {
            OracleModel model= setLevel(li.get(i), level);
            resList.add(li.get(i));
            level = 1;
        }

        StringBuilder sb = new StringBuilder();
        sb.append("[");
        for (int i = 0; i < resList.size(); i++) {
            ObjectMapper mapper = new ObjectMapper();
            String str = mapper.writeValueAsString(resList.get(i));
            if (i == (resList.size() - 1)) {
                sb.append(str);
            } else {

                sb.append(str).append(",");
            }
        }
        sb.append("]");
        String res = sb.toString();
        System.out.println(res);
    }

    public OracleModel getOracleModel(List list) {
        OracleModel om = new OracleModel();
        for (int i = 0; i < list.size(); i++) {
            if (list.get(i) instanceof OracleModel) {
                om = (OracleModel) list.get(i);
            } else {
                om = getOracleModel((List) list.get(i));
            }
        }
        return om;
    }

    public List<OracleModel> getOracleModel2(List list) {
        List<OracleModel> li = new ArrayList<>();
        OracleModel om = new OracleModel();
        for (int i = 0; i < list.size(); i++) {
            if (list.get(i) instanceof OracleModel) {
                om = (OracleModel) list.get(i);
            } else {
                om = getOracleModel((List) list.get(i));
            }
            li.add(om);
        }
        return li;
    }

    /**
     * @param cid  agent的id
     * @param conn 连接
     * @return 递归查询出来的proxy
     * @throws Exception 异常
     */
    public List<OracleModel> proxyTree(int cid, Connection conn, List<OracleModel> list) throws Exception {

        QueryRunner runner = new QueryRunner();
        /**
         * 根据父级查询子集的数据集合
         */
        String childsqlList = " select t.PROXYIP as ip, t.PROXYPORT as port, t.PROXYNAME as sysname, t.iid as id, n.PROXYNEXTID as pid from IEAI_PROXY_LIST t, IEAI_PROXY_LIST_RELATION n where n.PROXYNEXTTYPE=0 and t.IID = n.PROXYID and n.PROXYNEXTID = " + cid;
        List<OracleModel> queryList = runner.query(conn, childsqlList, new BeanListHandler<OracleModel>(OracleModel.class));
        List models = new ArrayList();
        if (queryList != null) {
            for (OracleModel proxy : queryList) {
                List sonList = new ArrayList();
                proxy.getNodes().add(list);
                proxy.setProxy(true);
                sonList.add(proxy);
                models.add(proxyTree(proxy.getId(), conn, sonList));
            }
        }
        if (null == models || models.isEmpty()) {
            return list;
        } else {
            return models;
        }
    }

    /**
     * 代理树查询
     *
     * @param cid       代理的父id
     * @param conn      连接
     * @param proxyInfo 上一级的代理信息
     * @return
     * @throws Exception
     */

    public List<OracleModel> proxyTree(int cid, Connection conn, OracleModel proxyInfo) throws Exception {
        QueryRunner runner = new QueryRunner();

        /**
         * 根据父级查询子集的数据集合
         */
        String childsqlList = " select t.PROXYIP as ip, t.PROXYPORT as port, t.PROXYNAME as sysname, t.iid as id, n.PROXYNEXTID as pid from IEAI_PROXY_LIST t, IEAI_PROXY_LIST_RELATION n where n.PROXYNEXTTYPE=0 and t.IID = n.PROXYID and n.PROXYNEXTID = " + cid;
        List<OracleModel> queryList = runner.query(conn, childsqlList, new BeanListHandler<OracleModel>(OracleModel.class));
        List models = new ArrayList();
        if (queryList != null) {
            for (OracleModel proxy : queryList) {
                /**
                 * 如果查询到数据  就将上级的数据放置在list中当做子集
                 */
                proxy.getNodes().add(proxyInfo);
                proxy.setProxy(true);

                /**
                 * 递归调用
                 */
                models.add(proxyTree(proxy.getId(), conn, proxy));
            }
        }
        if (null == models || models.isEmpty()) {
            models.add(proxyInfo);
        }
        return models;
    }

    private OracleModel nginxTree(Integer id, Connection conn) throws SQLException {
        QueryRunner runner = new QueryRunner();
        List<OracleModel> list = new ArrayList<>();
        String childNginx = "  select t.iid as id ,t.VIPNAME as sysname ,t.VIPIP as ip ,t.VIPPORT  as port ,y.PROXYID as pid  from IEAI_NGINX_PROXY t,IEAI_NGINX_PROXY_RELATION y  where t.IID = y.VIPID and y.PROXYID = '" + id + "'";
        OracleModel model = runner.query(conn, childNginx, new BeanHandler<OracleModel>(OracleModel.class));
        return model;
    }

    /**
     * level赋值
     *
     * @param li 要赋值的集合
     * @return
     */
    private OracleModel setLevel(OracleModel li, int level) {
        List nodes = li.getNodes();
        li.setLevel(level);
        if (nodes.size() != 0) {
            Object o2 = nodes.get(0);
            if (o2 instanceof OracleModel) {
                level++;
                OracleModel o = (OracleModel) nodes.get(0);
                o.setLevel(level);
                this.setLevel(o, level);
            } else {
                List list = (List) nodes.get(0);
                li = (OracleModel) list.get(0);
                this.setLevel(li, level);
            }
        }else{
            if (li instanceof OracleModel) {
                level++;
                li.setLevel(level);
            }
        }
        return li;
    }

    public OracleModel getOracleModel3(List list) {
        OracleModel om = new OracleModel();
        for (int i = 0; i < list.size(); i++) {
            if (list.get(i) instanceof OracleModel) {
                om = (OracleModel) list.get(i);
            } else {
                om = getOracleModel((List) list.get(i));
            }
        }
        return om;
    }

}
